package com.bjxst.dao.impl;

import com.bjxst.commons.JdbcUtils;
import com.bjxst.dao.UserManagerDao;
import com.bjxst.pojo.Users;

import javax.sql.rowset.JdbcRowSet;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

/*用户管理持久层*/
public class UserMangerDaoImpl implements UserManagerDao {
    @Override
    public void insertUser(Users users) {
        //添加用户
        Connection conn = null;
        try{

            conn = JdbcUtils.getConnection();
            //可以自动提交，但是设置为手动提交，让我们有事务的概念
            conn.setAutoCommit(false);//关闭事务处自动提交
            //执行的sql语句
            PreparedStatement ps = conn.prepareStatement("insert into users values(default ,?,?,?,?,?) ");
            //绑定参数
            ps.setString(1,users.getUsername());
            ps.setString(2,users.getUserpwd());
            ps.setString(3,users.getUsersex());
            ps.setString(4,users.getPhonenumber());
            ps.setString(5,users.getQqnumber());
            ps.execute();//执行语句
            conn.commit();//提交

        }catch (Exception e){
            e.printStackTrace();
            //出现异常  事务回滚
            JdbcUtils.rollbackConnection(conn);

        }finally {
            JdbcUtils.closeConnection(conn);
        }
    }

    //查询用户
    @Override
    public List<Users> selectUserByProperty(Users users) {
        Connection conn = null;
        List<Users> list = new ArrayList();
        try{
            conn=JdbcUtils.getConnection();
            //根据用户传过来的语句进行查询 传过来的有各种查询条件，所以要另外创建一个方法获取sql语句
            String sql = this.createSQL(users);
            PreparedStatement ps = conn.prepareStatement(sql);
            ResultSet resultSet = ps.executeQuery();
            while(resultSet.next()){
                Users user = new Users();
                //把查到的数据放进去
                user.setUserid(resultSet.getInt("userid"));
                user.setPhonenumber(resultSet.getString("phonenumber"));
                user.setQqnumber(resultSet.getString("qqnumber"));
                user.setUsersex(resultSet.getString("usersex"));
                user.setUserpwd(resultSet.getString("userpwd"));
                user.setUsername(resultSet.getString("username"));
                list.add(user);
            }
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            JdbcUtils.closeConnection(conn);
        }
        return list;
    }



    //拼接查询的sql语句
    private String createSQL(Users users) {
        //先创建一个StringBuffer  1=1确保条件成功  也方便在后面加and条件
        StringBuffer stringBuffer = new StringBuffer("select * from users where 1=1 ");
        // 不只是null 还要有字符长度才确保有数据
        if(users.getUsername() != null && users.getUsername().length() > 0){
            //注意空格键 将字符隔开
            stringBuffer.append(" and username = "+users.getUsername());
        }
        if(users.getUsersex() != null && users.getUsersex().length() > 0 ){
            stringBuffer.append(" and usersex = "+users.getUsersex());
        }
        if(users.getQqnumber() != null && users.getQqnumber().length() >0){
            stringBuffer.append(" and qqnumber = "+users.getQqnumber());

        }
        if(users.getPhonenumber() != null && users.getPhonenumber().length() >0){
            stringBuffer.append(" and phonenumber = "+users.getPhonenumber());
        }
        return stringBuffer.toString();
    }

    /*预更新用户（找到要更新的用户） 根据用户ID查询用户*/
    @Override
    public Users selectByUserid(int userid) {
        Connection conn = null;
        Users user = null;
        try{
            conn = JdbcUtils.getConnection();
            //编缉sql语句
            PreparedStatement preparedStatement = conn.prepareStatement("select * from users where userid = ? ");
            preparedStatement.setInt(1,userid);
            ResultSet resultSet = preparedStatement.executeQuery();
            while (resultSet.next()){
                user = new Users();
                user.setUserid(resultSet.getInt("userid"));
                user.setUsername(resultSet.getString("username"));
                user.setUserpwd(resultSet.getString("userpwd"));
                user.setUsersex(resultSet.getString("usersex"));
                user.setPhonenumber(resultSet.getString("phonenumber"));
                user.setQqnumber(resultSet.getString("qqnumber"));
            }


        }catch (Exception e){

        }finally {
            JdbcUtils.closeConnection(conn);
        }
        return user;
    }
    /*更新用户*/
    @Override
    public void updateUserByUserid(Users users) {
        Connection conn = null;
        try{
            conn = JdbcUtils.getConnection();
            //关闭自动提交
            conn.setAutoCommit(false);
            PreparedStatement ps = conn.prepareStatement("update users set username=?,usersex=?,phonenumber=?,qqnumber=? where userid = ?");
            ps.setString(1,users.getUsername());
            ps.setString(2,users.getUsersex());
            ps.setString(3,users.getPhonenumber());
            ps.setString(4,users.getQqnumber());
            ps.setInt(5,users.getUserid());
            ps.execute();
            conn.commit();//提交
        }catch (Exception e){
            e.printStackTrace();
            JdbcUtils.rollbackConnection(conn);//回滚业务
        }finally {
            JdbcUtils.closeConnection(conn);
        }
    }

    @Override
    public void deleteUserByUserid(int userid) {
        Connection conn =null;
        try{
            conn =JdbcUtils.getConnection();
            conn.setAutoCommit(false);
            PreparedStatement ps = conn.prepareStatement("delete from users where userid = ?  ");
            ps.setInt(1,userid);
            ps.execute();//执行sql语句
            conn.commit();//提交
        }catch (Exception e){
            e.printStackTrace();
            JdbcUtils.rollbackConnection(conn);//提交失败后事务回滚
        }finally {
            JdbcUtils.closeConnection(conn);
        }
    }
}
